const mysql = require('mysql2/promise');

async function verifyBasicItemsImport () {
  const connection = await mysql.createConnection({
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    timezone: '+08:00'
  });

  try {
    console.log('🔍 验证基础物品导入结果...\n');

    // 基本统计
    const [stats] = await connection.execute(`
      SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN is_treasure_shop = 1 THEN 1 END) as treasure_shop_items,
        COUNT(CASE WHEN is_exp_shop = 1 THEN 1 END) as exp_shop_items,
        COUNT(CASE WHEN silver_price > 0 THEN 1 END) as items_with_silver_price,
        COUNT(CASE WHEN gold_price > 0 THEN 1 END) as items_with_gold_price,
        COUNT(CASE WHEN pic != '' THEN 1 END) as items_with_pic,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_items,
        COUNT(CASE WHEN status = 0 THEN 1 END) as inactive_items
      FROM basic_items
    `);

    console.log('📊 导入统计:');
    console.log(`  总物品数: ${stats[0].total_items}`);
    console.log(`  宝物商店物品: ${stats[0].treasure_shop_items}`);
    console.log(`  经验商店物品: ${stats[0].exp_shop_items}`);
    console.log(`  有银两价格物品: ${stats[0].items_with_silver_price}`);
    console.log(`  有金砖价格物品: ${stats[0].items_with_gold_price}`);
    console.log(`  有图片物品: ${stats[0].items_with_pic}`);
    console.log(`  启用物品: ${stats[0].active_items}`);
    console.log(`  禁用物品: ${stats[0].inactive_items}`);

    // 价格分布统计
    const [priceStats] = await connection.execute(`
      SELECT 
        MIN(silver_price) as min_silver_price,
        MAX(silver_price) as max_silver_price,
        AVG(silver_price) as avg_silver_price,
        MIN(gold_price) as min_gold_price,
        MAX(gold_price) as max_gold_price,
        AVG(gold_price) as avg_gold_price
      FROM basic_items
      WHERE silver_price > 0 OR gold_price > 0
    `);

    console.log('\n💰 价格统计:');
    console.log(`  银两价格范围: ${priceStats[0].min_silver_price} - ${priceStats[0].max_silver_price} 两`);
    console.log(`  银两平均价格: ${Math.round(priceStats[0].avg_silver_price)} 两`);
    console.log(`  金砖价格范围: ${priceStats[0].min_gold_price} - ${priceStats[0].max_gold_price} 金砖`);
    console.log(`  金砖平均价格: ${Math.round(priceStats[0].avg_gold_price)} 金砖`);

    // 等级分布统计
    const [levelStats] = await connection.execute(`
      SELECT 
        lv,
        COUNT(*) as count
      FROM basic_items
      GROUP BY lv
      ORDER BY lv
    `);

    console.log('\n📈 等级分布:');
    levelStats.forEach(stat => {
      console.log(`  等级 ${stat.lv}: ${stat.count} 个物品`);
    });

    // 商店类型统计
    const [shopStats] = await connection.execute(`
      SELECT 
        CASE 
          WHEN is_treasure_shop = 1 AND is_exp_shop = 1 THEN '宝物商店 + 经验商店'
          WHEN is_treasure_shop = 1 THEN '宝物商店'
          WHEN is_exp_shop = 1 THEN '经验商店'
          ELSE '未上架'
        END as shop_type,
        COUNT(*) as count
      FROM basic_items
      GROUP BY 
        CASE 
          WHEN is_treasure_shop = 1 AND is_exp_shop = 1 THEN '宝物商店 + 经验商店'
          WHEN is_treasure_shop = 1 THEN '宝物商店'
          WHEN is_exp_shop = 1 THEN '经验商店'
          ELSE '未上架'
        END
      ORDER BY count DESC
    `);

    console.log('\n🏪 商店类型分布:');
    shopStats.forEach(stat => {
      console.log(`  ${stat.shop_type}: ${stat.count} 个物品`);
    });

    // 显示一些示例数据
    console.log('\n📋 示例数据 (前20个物品):');
    const [samples] = await connection.execute(`
      SELECT 
        name, 
        silver_price, 
        gold_price, 
        lv, 
        weight,
        is_treasure_shop, 
        is_exp_shop, 
        pic,
        LEFT(description, 30) as short_desc
      FROM basic_items
      ORDER BY id
      LIMIT 20
    `);

    samples.forEach((item, index) => {
      const shops = [];
      if (item.is_treasure_shop) shops.push('宝物商店');
      if (item.is_exp_shop) shops.push('经验商店');
      const shopInfo = shops.length > 0 ? ` (${shops.join(', ')})` : '';

      const priceInfo = [];
      if (item.silver_price > 0) priceInfo.push(`${item.silver_price}两`);
      if (item.gold_price > 0) priceInfo.push(`${item.gold_price}金砖`);
      const priceStr = priceInfo.length > 0 ? priceInfo.join(' + ') : '免费';

      console.log(`  ${index + 1}. ${item.name} - ${priceStr} - 等级${item.lv} - 负重${item.weight}${shopInfo}`);
      console.log(`     描述: ${item.short_desc}...`);
      console.log(`     图片: ${item.pic}`);
    });

    // 检查重复名称
    const [duplicates] = await connection.execute(`
      SELECT name, COUNT(*) as count
      FROM basic_items
      GROUP BY name
      HAVING COUNT(*) > 1
    `);

    if (duplicates.length > 0) {
      console.log('\n⚠️ 发现重复物品名称:');
      duplicates.forEach(dup => {
        console.log(`  ${dup.name}: ${dup.count} 个重复`);
      });
    } else {
      console.log('\n✅ 无重复物品名称');
    }

    // 检查空值
    const [nullChecks] = await connection.execute(`
      SELECT 
        COUNT(CASE WHEN name = '' OR name IS NULL THEN 1 END) as empty_names,
        COUNT(CASE WHEN description = '' OR description IS NULL THEN 1 END) as empty_descriptions,
        COUNT(CASE WHEN pic = '' OR pic IS NULL THEN 1 END) as empty_pics
      FROM basic_items
    `);

    console.log('\n🔍 数据完整性检查:');
    console.log(`  空名称: ${nullChecks[0].empty_names} 个`);
    console.log(`  空描述: ${nullChecks[0].empty_descriptions} 个`);
    console.log(`  空图片: ${nullChecks[0].empty_pics} 个`);

    // 特殊物品检查
    console.log('\n🎯 特殊物品检查:');

    // 检查高价值物品
    const [highValue] = await connection.execute(`
      SELECT name, silver_price, gold_price, lv
      FROM basic_items
      WHERE silver_price >= 1000 OR gold_price >= 100
      ORDER BY (silver_price + gold_price * 100) DESC
      LIMIT 10
    `);

    console.log('  高价值物品 (前10个):');
    highValue.forEach(item => {
      const totalValue = item.silver_price + item.gold_price * 100;
      console.log(`    ${item.name} - 总价值: ${totalValue} (${item.silver_price}两 + ${item.gold_price}金砖) - 等级${item.lv}`);
    });

    // 检查经验相关物品
    const [expItems] = await connection.execute(`
      SELECT name, silver_price, lv, is_exp_shop
      FROM basic_items
      WHERE name LIKE '%经验%' OR description LIKE '%经验%'
      ORDER BY silver_price DESC
      LIMIT 10
    `);

    console.log('\n  经验相关物品:');
    expItems.forEach(item => {
      const shopInfo = item.is_exp_shop ? ' (经验商店)' : '';
      console.log(`    ${item.name} - ${item.silver_price}两 - 等级${item.lv}${shopInfo}`);
    });

    console.log('\n🎉 基础物品导入验证完成！');

  } catch (error) {
    console.error('❌ 验证失败:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
  } finally {
    await connection.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

// 运行脚本
if (require.main === module) {
  verifyBasicItemsImport().catch(console.error);
}

module.exports = { verifyBasicItemsImport };
